pacman::p_load(tidyverse, haven, stringi, countrycode)

# TABLE A1: number of firms with owners in foreign locations -------
# load the main set of oligarch-controlled firms from Delo/UP
DUP <- read_dta("Delo_UP.dta") %>%
  select(okpo, group) %>%
  mutate(political_alignment = case_when(
    group=="Derkach" | group=="Energo" | group=="Franchuk" | 
      group=="Interpipe" | group=="Khmelnytskyi" | 
      group=="Kirpa" | group=="Kliuev" | group=="Kyiv-Seven" | 
      group=="Old Donetsk" | group=="Radon" | group=="SCM" | 
      group=="Tabachnyk" | group=="TAS" | group=="Ukrinterproduct" ~ "Blue",
    group=="Aval" | group=="Brinkford" | group=="Finansy i Kredyt" | 
      group=="Ignatenko" | group=="Ivchenko" | group=="Omelchenko" | 
      group=="Orlan" | group=="Rybachuk" | group=="Rymaruk" | 
      group=="Skomarovskyi" | group=="Tretiakov" | 
      group=="UkrPromInvest" | group=="Yushchenko" | 
      group=="Zagreva" | group=="Pryvat" ~ "Orange",
    group=="Basis" | group=="Feldman" | group=="Intercontact"  | 
      group=="ISD" | group=="Kahanovskyi" | group=="UkrSotsBank" | 
      group=="UkrSybBank" | group=="double SCM/ISD" | 
      group=="double ISD/Pryvat" ~ "Gray"
  ))

# load codes of countries designated as offshore
offshore_codes <- read_csv("Additional Data/offshore_codes.csv")

# TABLE 5: number of firms with foreign and offshore entities in chain -------
# load regression sample for foreign in chain/offshore in chain, baseline data
reg_sample_foreign_baseline <- read_dta("Additional Data/sample299.dta") %>%
  left_join(DUP)

# load regression sample for foreign in chain/offshore in chain, JSCReg data
reg_sample_foreign_JSCReg <- read_dta("Additional Data/sample229.dta") %>%
  left_join(DUP)

# load measures of defensive ownership for 2004 and 2006, baseline data
do_base_2004 <- read_dta("Network Data/DUP_hiding_04012004.dta") %>%
  select(c(okpo, group, foreign_in_chain, offshore_in_chain,
           foreign_entities_countries))
do_base_2006 <- read_dta("Network Data/DUP_hiding_11102006.dta") %>%
  select(c(okpo, group, foreign_in_chain, offshore_in_chain,
           foreign_entities_countries))

# limit to the regression sample
do_base_2004 <- left_join(reg_sample_foreign_baseline, do_base_2004)
do_base_2006 <- left_join(reg_sample_foreign_baseline, do_base_2006)

# load measures of defensive ownership for 2004 and 2006, JSCReg data
do_JSCReg_2004 <- 
  read_dta("Network Data/DUP_hiding_04012004_JSCReg_only.dta") %>%
  select(c(okpo, group, foreign_in_chain, offshore_in_chain,
           foreign_entities_countries))
do_JSCReg_2006 <- 
  read_dta("Network Data/DUP_hiding_11102006_JSCReg_only.dta") %>%
  select(c(okpo, group, foreign_in_chain, offshore_in_chain,
           foreign_entities_countries))

# limit to the regression sample
do_JSCReg_2004 <- left_join(reg_sample_foreign_JSCReg, do_JSCReg_2004)
do_JSCReg_2006 <- left_join(reg_sample_foreign_JSCReg, do_JSCReg_2006)

# summarize defensive ownership by political alignment
do_sums <- list(do_base_2004, do_base_2006,
                do_JSCReg_2004, do_JSCReg_2006) %>%
  purrr::map(~.x %>%
               select(-foreign_entities_countries) %>%
               group_by(political_alignment) %>%
               summarise(across(c(foreign_in_chain, offshore_in_chain),
                                sum)) %>%
               add_row(tibble(political_alignment = "Total",
                              foreign_in_chain =
                                sum(.x$foreign_in_chain),
                              offshore_in_chain =
                                sum(.x$offshore_in_chain))) %>%
               pivot_longer(-political_alignment,
                            names_to = "Variable",
                            values_to = "Count") %>%
               mutate(political_alignment = 
                        factor(political_alignment,
                               levels = c("Total", "Blue",
                                          "Orange", "Gray"))) %>%
               arrange(Variable, political_alignment) %>%
               mutate(political_alignment = as.character(political_alignment),
                      political_alignment = if_else(
                        political_alignment == "Total", Variable, political_alignment
                      )) %>%
               select(-Variable))
do_sums <- bind_cols(do_sums[[1]] %>% rename(`Baseline 2004` = Count),
                     do_sums[[2]] %>% select(Count) %>% 
                       rename(`Baseline 2006` = Count),
                     do_sums[[3]] %>% select(Count) %>% 
                       rename(`JSCReg 2004` = Count),
                     do_sums[[4]] %>% select(Count) %>% 
                       rename(`JSCReg 2006` = Count)) %>%
  mutate(political_alignment = case_when(
    political_alignment == "foreign_in_chain" ~ "Foreign",
    political_alignment == "offshore_in_chain" ~ "Offshore",
    TRUE ~ political_alignment
  ))

write_csv(do_sums, "Results/Table_5.csv")


# TABLE A1: number of firms with owners in foreign locations -------
# create data frames of foreign locations (countries of foreign owner)
# per oligarch firm; each row is firm-country
# (firms without foreign owners are dropped)
countries_2004 <- do_base_2004 %>% 
  mutate(foreign_entities_countries = 
           strsplit(foreign_entities_countries, "|", fixed = T)) %>%
  unnest(foreign_entities_countries) %>%
  mutate(foreign_entities_countries = as.numeric(foreign_entities_countries))
countries_2006 <- do_base_2006 %>% 
  mutate(foreign_entities_countries = 
           strsplit(foreign_entities_countries, "|", fixed = T)) %>%
  unnest(foreign_entities_countries) %>%
  mutate(foreign_entities_countries = as.numeric(foreign_entities_countries))

count_by_country_2004 <- countries_2004 %>%
  group_by(foreign_entities_countries) %>%
  summarise(n = n()) %>%
  mutate(country = if_else(foreign_entities_countries == 830,
                           "Channel Islands",
                           countrycode(foreign_entities_countries,
                                "iso3n", "country.name")),
         offshore = if_else(foreign_entities_countries %in%
                              offshore_codes$country_code,
                            1, 0)) %>%
  arrange(offshore, desc(n))

# select countries to display in the table and group the rest as "other"
keep_offshore <- count_by_country_2004 %>%
  filter(offshore == 1, n > 10 | foreign_entities_countries == 292) %>%
  pull(foreign_entities_countries)
keep_non_offshore <- count_by_country_2004 %>%
  filter(offshore == 0, n > 10) %>%
  pull(foreign_entities_countries)

# re-calculate counts grouping other offshore/other non-offshore 
# countries together
count_by_country <- list(countries_2004, countries_2006) %>%
  purrr::map(~.x %>% 
               mutate(offshore = if_else(foreign_entities_countries %in%
                                           offshore_codes$country_code, 1, 0),
                      foreign_entities_countries = case_when(
                        foreign_entities_countries %in% c(keep_offshore,
                                                          keep_non_offshore) ~ 
                          foreign_entities_countries,
                        !foreign_entities_countries %in% c(keep_offshore,
                                                           keep_non_offshore) &
                          offshore == 1 ~ 9999,
                        !foreign_entities_countries %in% c(keep_offshore,
                                                           keep_non_offshore) &
                          offshore == 0 ~ 8888))  %>%
               distinct(foreign_entities_countries, okpo) %>%
               group_by(foreign_entities_countries) %>%
               summarise(N = n()) %>%
               mutate(Country = case_when(
                 foreign_entities_countries == 9999 ~ "Other offshore",
                 foreign_entities_countries == 8888 ~ "Other non-offshore",
                 TRUE ~ countrycode(foreign_entities_countries,
                                    "iso3n", "country.name")),
                 offshore = if_else(foreign_entities_countries %in%
                                      offshore_codes$country_code | 
                                      foreign_entities_countries == 9999,
                                    1, 0),
                 Share = round(N/nrow(reg_sample_foreign_baseline), 3)) %>%
               arrange(desc(offshore), desc(N)) %>%
               select(Country, N, Share, offshore))
  
# merge 2004 and 2006
count_by_country <- full_join(count_by_country[[1]] %>%
                                rename(`N 2004` = N,
                                       `Share 2004` = Share),
                              count_by_country[[2]] %>%
                                rename(`N 2006` = N,
                                       `Share 2006` = Share))

# total count/share of offshore/non-offshore for 2004 and 2006
offshore_total <- list(countries_2004, countries_2006) %>%
  purrr::map_dbl(~.x %>%
                   filter(foreign_entities_countries %in%
                            offshore_codes$country_code) %>%
                   distinct(okpo) %>% nrow())
non_offshore_total <- list(countries_2004, countries_2006) %>%
  purrr::map_dbl(~.x %>%
                   filter(!foreign_entities_countries %in%
                            offshore_codes$country_code) %>%
                   distinct(okpo) %>% nrow())
total_count <- rbind(offshore_total, non_offshore_total) %>%
  as_tibble() %>%
  mutate(`Share 2004` = round(V1/nrow(reg_sample_foreign_baseline), 3),
         `Share 2006` = round(V2/nrow(reg_sample_foreign_baseline), 3)) %>%
  rename(`N 2004` = V1, `N 2006` = V2) %>%
  add_column(Country = c("Offshore locations", 
                         "Non-offshore locations")) %>%
  select(Country, `N 2004`, `N 2006`, `Share 2004`, `Share 2006`)

table_a1 <- bind_rows(
  total_count[1, ],
  count_by_country %>% filter(offshore == 1, 
                              Country != "Other offshore") %>%
    select(-offshore),
  count_by_country %>% filter(Country == "Other offshore") %>%
    select(-offshore),
  total_count[2, ],
  count_by_country %>% filter(offshore == 0, 
                              Country != "Other non-offshore") %>%
    select(-offshore),
  count_by_country %>% filter(Country == "Other non-offshore") %>%
    select(-offshore),
)

write_csv(table_a1, "Results/Table_A1.csv")


# TABLE A2: number of firms with defensive ownership, by oligarch group ------
# load regression sample for no oligarch in chain, baseline data
reg_sample_oligarch_baseline <- read_dta("Additional Data/sample329.dta") %>%
  left_join(DUP)

# data frame for no oligarch in chain (baseline data)
no_base_2004 <- read_dta("Network Data/DUP_hiding_04012004.dta") %>%
  select(c(okpo, group, oligarch_in_chain, foreign_in_chain, offshore_in_chain)) 
no_base_2004 <- reg_sample_oligarch_baseline %>%
  left_join(no_base_2004) %>%
  mutate(no_oligarch = 1 - oligarch_in_chain)

no_base_2006 <- read_dta("Network Data/DUP_hiding_11102006.dta") %>%
  select(c(okpo, group, oligarch_in_chain, foreign_in_chain, offshore_in_chain)) 
no_base_2006 <- reg_sample_oligarch_baseline %>%
  left_join(no_base_2006) %>%
  mutate(no_oligarch = 1 - oligarch_in_chain)

no_oligarch_sum <- no_base_2004 %>%
  group_by(group) %>%
  summarise(`No oligarch 2004` = sum(no_oligarch))

foreign_2004_sum <- do_base_2004 %>%
  group_by(group) %>%
  summarise(`Foreign 2004` = sum(foreign_in_chain),
            `Offshore 2004` = sum(offshore_in_chain))

foreign_2006_sum <- do_base_2006 %>%
  group_by(group) %>%
  summarise(`Foreign 2006` = sum(foreign_in_chain),
            `Offshore 2006` = sum(offshore_in_chain))

total_oligarch <- no_base_2004 %>%
  group_by(group) %>%
  summarise(`Oligarch sample` = n())

total_foreign <- do_base_2004 %>%
  group_by(group) %>%
  summarise(`Foreign sample` = n())

table_a2 <- full_join(no_oligarch_sum, foreign_2004_sum) %>%
  full_join(foreign_2006_sum) %>%
  full_join(total_oligarch) %>%
  full_join(total_foreign) %>%
  select(group, `No oligarch 2004`, `Foreign 2004`, `Foreign 2006`,
         `Offshore 2004`, `Offshore 2006`, `Oligarch sample`, `Foreign sample`)
table_a2 <- bind_rows(table_a2 %>%
                        filter(!str_detect(group, "double")),
                      table_a2 %>%
                        filter(str_detect(group, "double")) %>%
                        mutate(group = str_remove(group, "double "))) %>%
  add_row(group = "Total",
          `No oligarch 2004` = sum(no_base_2004$no_oligarch),
          `Foreign 2004` = sum(do_base_2004$foreign_in_chain),
          `Foreign 2006` = sum(do_base_2006$foreign_in_chain),
          `Offshore 2004` = sum(do_base_2004$offshore_in_chain),
          `Offshore 2006` = sum(do_base_2006$offshore_in_chain),
          `Oligarch sample` = nrow(no_base_2004),
          `Foreign sample` = nrow(do_base_2004))
write_csv(table_a2, "Results/Table_A2.csv")

